package celcatSynchronizer;

import com.google.gdata.data.calendar.CalendarEntry;
import com.google.gdata.data.calendar.CalendarEventEntry;
import com.google.gdata.data.extensions.When;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.TimeZone;

/**
 * Comparateur entre les event de Google Calendars et la bd Celcat.
 */
public class DbComparator
{
	/**
	 * Parsing du jour de la semaine du GregorianCalendar vers [1..7] pour [Lundi..Dimanche].
	 * @param p_day Le jour version GregorianCalendar.
	 * @return Le numéro adéquat.
	 */
	private int parseDayOfWeek(int p_day) throws IOException
	{
		switch(p_day)
		{
			case GregorianCalendar.MONDAY:
				return 1;
			case GregorianCalendar.TUESDAY:
				return 2;
			case GregorianCalendar.WEDNESDAY:
				return 3;
			case GregorianCalendar.THURSDAY:
				return 4;
			case GregorianCalendar.FRIDAY:
				return 5;
			case GregorianCalendar.SATURDAY:
				return 6;
			case GregorianCalendar.SUNDAY:
				return 7;
		}
		
		throw new IOException("Invalid day of week");
	}
	
	/**
	 * Enregistrement d'un couple eventId/week dans la HashMap des éléments traités.
	 * @param p_processedEntries La HashMap.
	 * @param p_id L'id.
	 * @param p_week La week.
	 */
	private void registerEntry(HashMap< Integer, HashSet<Integer> > p_processedEntries, int p_id, int p_week)
	{
		HashSet<Integer> weeks = p_processedEntries.get(p_id);
		if(weeks == null)
		{
			weeks = new HashSet<Integer>();
			p_processedEntries.put(p_id, weeks);
		}
		
		weeks.add(p_week);
	}
	
	/**
	 * Fonction de comparaison.
	 * @param p_calendar Le calendrier Google.
	 * @param p_googleInterface L'interface avec Google Calendar.
	 * @param p_db La db.
	 * @param p_groupId L'id du groupe sélectionné.
	 */
	public void compareToDb(CalendarEntry p_calendar, GoogleInterface p_googleInterface, Connection p_db, int p_groupId) throws IOException, SQLException, Exception
	{
		// HashMap des events déjà traités.
		HashMap< Integer, HashSet<Integer> > processedEntries = new HashMap< Integer, HashSet<Integer> >();
		
		PreparedStatement statEvent = p_db.prepareStatement(
"SELECT CT_EVENT._event_id " +
"FROM CT_EVENT, CT_EVENT_WEEK, CT_EVENT_GROUP " +
"WHERE " +
	"CT_EVENT_WEEK._event_id = CT_EVENT._event_id AND " +
	"CT_EVENT_GROUP._event_id = CT_EVENT._event_id AND " +
	"_day_of_week = ? AND " +
	"_start_time = ? AND " +
	"_end_time = ? AND " +
	"_week = ? AND " +
	"_group_id = ?"
		);
		
		PreparedStatement statEventAdd = p_db.prepareStatement("INSERT INTO CT_EVENT (_event_id, _day_of_week, _start_time, _end_time) VALUES ((SELECT MAX(_event_id) + 1 FROM CT_EVENT), ?, ?, ?)");
		PreparedStatement statEventAddWeek = p_db.prepareStatement("INSERT INTO CT_EVENT_WEEK (_event_id, _week) VALUES (?, ?)");
		PreparedStatement statEventAddGroup = p_db.prepareStatement("INSERT INTO CT_EVENT_GROUP (_event_id, _group_id) VALUES (?, ?)");
		PreparedStatement statEventAddModule = p_db.prepareStatement("INSERT INTO CT_EVENT_MODULE (_event_id, _module_id) VALUES (?, (SELECT _module_id FROM CT_MODULE WHERE _name = ?))");
		
		// On récupère la semaine de référence (semaine 1).
		GregorianCalendar startWeek = new GregorianCalendar();
		startWeek.setTimeInMillis(Long.parseLong(CelcatSynchronizer.config.getProperty("startWeek")));
		
		// Pour chaque event, on va rechercher s'il existe une entrée dans la base de donnée Celcat.
		// Si c'est le cas, on ne fait qu'ajouter l'event à la liste des event traités.
		// Si ce n'est pas le cas, on ajoute l'event dans la base Celcat et on l'ajoute de même à la liste des events traités.
		ArrayList<CalendarEventEntry> eventEntryList = p_googleInterface.getEvents(p_calendar);
		for(CalendarEventEntry event : eventEntryList)
		{
			for(When eventTime : event.getTimes())
			{
				GregorianCalendar gcStart = new GregorianCalendar();
				gcStart.setTimeInMillis(eventTime.getStartTime().getValue());
				gcStart.setTimeZone(TimeZone.getTimeZone("Europe/Paris"));
				int eventTimeStart = gcStart.get(GregorianCalendar.HOUR_OF_DAY) * 60 + gcStart.get(GregorianCalendar.MINUTE);

				GregorianCalendar gcEnd = new GregorianCalendar();
				gcEnd.setTimeInMillis(eventTime.getEndTime().getValue());
				gcEnd.setTimeZone(TimeZone.getTimeZone("Europe/Paris"));
				int eventTimeEnd = gcEnd.get(GregorianCalendar.HOUR_OF_DAY) * 60 + gcEnd.get(GregorianCalendar.MINUTE);

				// We don't process multi-day events ("Semaine Spécialité")
				if(gcStart.get(GregorianCalendar.DAY_OF_YEAR) != gcEnd.get(GregorianCalendar.DAY_OF_YEAR))
					continue;
				
				int eventDay = parseDayOfWeek(gcStart.get(GregorianCalendar.DAY_OF_WEEK));
				String eventModule = event.getTitle().getPlainText().split("-")[0].trim();
				
				int eventWeek = gcStart.get(GregorianCalendar.WEEK_OF_YEAR) - startWeek.get(GregorianCalendar.WEEK_OF_YEAR);
				if(eventWeek <= 0)
					eventWeek += gcStart.getActualMaximum(GregorianCalendar.WEEK_OF_YEAR);

				statEvent.setInt(1, eventDay);
				statEvent.setInt(2, eventTimeStart);
				statEvent.setInt(3, eventTimeEnd);
				statEvent.setInt(4, eventWeek);
				statEvent.setInt(5, p_groupId);
				ResultSet rsEvent = statEvent.executeQuery();
				
				if(rsEvent.next())
					registerEntry(processedEntries, rsEvent.getInt("_event_id"), eventWeek);
				else
				{
					// L'event Google n'existe pas dans le CSV, on l'ajoute
					statEventAdd.setInt(1, eventDay);
					statEventAdd.setInt(2, eventTimeStart);
					statEventAdd.setInt(3, eventTimeEnd);
					statEventAdd.execute();
					
					ResultSet rsKey = statEventAdd.getGeneratedKeys();
					rsKey.next();
					int eventId = rsKey.getInt(1);
					
					statEventAddWeek.setInt(1, eventId);
					statEventAddWeek.setInt(2, eventWeek);
					statEventAddWeek.execute();
					
					statEventAddGroup.setInt(1, eventId);
					statEventAddGroup.setInt(2, p_groupId);
					statEventAddGroup.execute();
					
					try
					{
						statEventAddModule.setInt(1, eventId);
						statEventAddModule.setString(2, eventModule);
						statEventAddModule.execute();
					}
					catch(SQLException ex)
					{
						CelcatSynchronizer.log.warning("A module has been skipped due to an invalid spelling [" + eventModule + "]");
					}
					
					registerEntry(processedEntries, eventId, eventWeek);
				}
			}
		}
		
		PreparedStatement statEventSelect = p_db.prepareStatement("SELECT _event_id FROM CT_EVENT_GROUP WHERE _group_id = ?");
		PreparedStatement statEventSelectWeek = p_db.prepareStatement("SELECT _week FROM CT_EVENT_WEEK WHERE _event_id = ?");
		PreparedStatement statEventRemoveWeek = p_db.prepareStatement("DELETE FROM CT_EVENT_WEEK WHERE _event_id = ? AND _week = ?");
		PreparedStatement statEventCountWeek = p_db.prepareStatement("SELECT COUNT(_week) FROM CT_EVENT_WEEK WHERE _event_id = ?");
		PreparedStatement statEventRemove = p_db.prepareStatement("DELETE FROM CT_EVENT WHERE _event_id = ?");
		
		// On parcours tous les events du groupe sélectionné.
		// Pour chaque entrée, on vérifie qu'elle a été traitée.
		// Si c'est le cas ... temps mieux.
		// Sinon, on supprime l'event à la semaine concernée.
		// Si l'event ne possède plus d'occurences dans les semaines, on le supprime.
		ResultSet rsEventSelect = statEventSelect.executeQuery();
		while(rsEventSelect.next())
		{
			int eventId = rsEventSelect.getInt(0);
			if(!processedEntries.containsKey(eventId))
			{
				statEventRemove.setInt(1, eventId);
				statEventRemove.execute();
			}
			else
			{
				HashSet<Integer> processedWeeks = processedEntries.get(eventId);
				statEventSelectWeek.setInt(1, eventId);
				ResultSet rsEventSelectWeek = statEventSelectWeek.executeQuery();
				while(rsEventSelectWeek.next())
				{
					int week = rsEventSelectWeek.getInt("_week");
					if(!processedWeeks.contains(week))
					{
						statEventRemoveWeek.setInt(1, eventId);
						statEventRemoveWeek.setInt(2, week);
						statEventRemoveWeek.execute();
					}
				}

				statEventCountWeek.setInt(1, eventId);
				ResultSet rsEventCountWeek = statEventCountWeek.executeQuery();
				rsEventCountWeek.next();
				if(rsEventCountWeek.getInt(0) == 0)
				{
					statEventRemove.setInt(1, eventId);
					statEventRemove.execute();
				}
			}
		}
	}
}
